package com.zhike.b2b.product.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.servlet.ServletContext;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import com.zhike.b2b.Constants;
import com.zhike.b2b.Query;
import com.zhike.b2b.base.BaseService;
import com.zhike.b2b.company.ICompanyService;
import com.zhike.b2b.html.ILinkService;
import com.zhike.b2b.model.ClassVo;
import com.zhike.b2b.model.CompanyVo;
import com.zhike.b2b.model.LinkVo;
import com.zhike.b2b.model.NewsClassVo;
import com.zhike.b2b.model.ProductAttrVo;
import com.zhike.b2b.model.ProductClassVo;
import com.zhike.b2b.model.ProductVo;
import com.zhike.b2b.model.UserInfo;
import com.zhike.b2b.product.IProdClassService;
import com.zhike.b2b.product.IProductService;
import com.zhike.b2b.util.ClassUtil;
import com.zhike.b2b.util.PageTool;
import com.zhike.b2b.util.StringUtil;

@Service
public class ProductService extends BaseService implements IProductService {
	@Resource
	private ICompanyService cs;
	@Resource
	private IProdClassService pcs;
	@Resource
	protected ILinkService ls;
	
	/**
	 * 获得最新发布的
	 * @param start
	 * @param size
	 * @return
	 */
	public List<ProductVo> getNewestProducts(int start, int size)
	  {
		  String sql = "  select p.product_id product_id ,p.class_id class_id,p.product_name,publish_date,sc.article_rule,sc.list_rule ,sc.store_path store_path from product p left join product_class as sc on sc.class_id = p.class_id where p.display_start_date <= now() and p.display_end_date >= now() and p.validity = 1  and p.display=1  order by  p.oper_time desc  ";
		  sql += " limit "+start+","+size ;
		
		  List<ProductVo> lnc = jt.query(sql.toString(), new RowMapper<ProductVo>() {
				public ProductVo mapRow(ResultSet rs, int rowNum) throws SQLException {
					ProductVo pv = new ProductVo();
					pv.setClassId(rs.getString("class_id"));
					pv.setProductId(rs.getString("product_id"));
					pv.setListRule(rs.getString("list_rule"));
					pv.setStorePath(rs.getString("store_path"));
					pv.setArticleRule(rs.getString("article_rule"));
					pv.setProductName(rs.getString("product_name"));
					pv.setPublishTime(rs.getString("publish_date"));
					pv.setUrl(ClassUtil.getObjUrl(pv, pv.getProductId()));
					return pv;
				}
			});
			return lnc;
	  }
	
	  
	/**
	 * 根据条件查询产品(供应)列表
	 * 
	 * @param classId
	 * @param limitStr
	 * @param orderCln
	 * @param commend
	 * @return
	 */
	public List<ProductVo> queryProducts(String classId,String commend ,String limitStr,
			String orderCln) {
	   StringBuffer sql = new StringBuffer("select distinct p.product_id,p.product_name,p.PRODUCT_UNIT PRODUCT_UNIT,p.PRODUCT_PRICE price,p.attach_id,a.file_path prod_photo,p.class_id,p.MONEYTYPE,p.oper_time,p.small_img,p.big_img,pc.list_rule,pc.store_path,pc.article_rule,p.commend,p.good_product,p.list_top,p.num ");
	   
       sql.append(" from product p  left join attach a on p.attach_id = a.attach_root_id inner join product_class pc on pc.class_id=p.class_id where p.display_start_date <=now() and p.display_end_date >=now() and p.display=1 and p.validity =1 ");
	    if (!classId.equals(""))
	    	sql.append(" and p.class_id like '" + classId + "%'");
	    if (!commend.equals(""))
	    	sql.append(" and p.commend=" + commend);
	    if (orderCln.equals(""))
	        sql.append("  order by p." +orderCln +" desc");
	    if (!limitStr.equals(""))
	    	sql.append(" limit " + limitStr );
		List<ProductVo> lnc = jt.query(sql.toString(), new RowMapper<ProductVo>() {
			public ProductVo mapRow(ResultSet rs, int rowNum) throws SQLException {
				ProductVo pv = new ProductVo();
				pv.setClassId(rs.getString("class_id"));
				pv.setProductId(rs.getString("product_id"));
				pv.setListRule(rs.getString("list_rule"));
				pv.setStorePath(rs.getString("store_path"));
				pv.setArticleRule(rs.getString("article_rule"));
				pv.setProductName(rs.getString("product_name"));
				pv.setPrice(rs.getDouble("price"));
				pv.setOperTime(rs.getString("oper_time"));
				String moneyType = rs.getString("MONEYTYPE");
				moneyType = ps.getDataParam("moneytype", moneyType);
				pv.setMoneytype(moneyType);
				String unit = rs.getString("PRODUCT_UNIT");
				unit = ps.getDataParam("product_unit", unit);
				pv.setUnit(unit);
				
				String smallImg = rs.getString("prod_photo");
				if(smallImg == null || smallImg.trim().length()<1)
					smallImg = defaultLogoImg;
				pv.setSmallImg(smallImg);
				
				pv.setCommend(rs.getString("commend"));
				pv.setBigImg(rs.getString("big_img"));
				pv.setNum(rs.getString("num"));
                pv.setListTop(rs.getString("list_top"));
				pv.setUrl(ClassUtil.getObjUrl(pv, pv.getProductId()));
				return pv;
			}
		});
		return lnc;
	}

	
	  public String getProdSqlstr(String orderStr, String limitStr, String commend, String classId, String custId, String isHot, String isGood, String levelId, String type, String custType)
	  {
	    StringBuffer sql = new StringBuffer("select distinct p.product_id,p.product_name,p.attach_id,p.class_id,p.small_img,p.big_img,p.commend,p.good_product,p.list_top,p.num,ai.area_name ");
	    
	    StringBuffer subSql = new StringBuffer(" from(select p.attach_id,p.product_id,p.cust_id,p.area_id,p.product_name,p.class_id,p.small_img,p.big_img,p.commend,p.good_product,p.list_top,p.num,p.publish_user_id");
	   
	    subSql.append(" from product p inner join product_class pc on pc.class_id=p.class_id where p.display_start_date <=now() and p.display_end_date >=now() and p.display=1 and p.validity =1 ");
	    if (!classId.equals(""))
	      subSql.append(" and p.class_id like '" + classId + "%'");
	    if (!commend.equals(""))
	      subSql.append(" and p.commend=" + commend);
	    
	    if (!isHot.equals(""))
	      subSql.append(" and p.hot_product=" + isHot);
	    if (!isGood.equals(""))
	      subSql.append(" and p.good_product=" + isGood);
	    if (!custId.equals(""))
	      subSql.append(" and p.cust_id=" + custId);
	    if (orderStr.equals(""))
	    {
	      subSql.append("  order by p.commend  desc  ,p.good_product desc,p.hot_product desc ,p.list_top desc,p.publish_date desc ");
	    }
	    if (!limitStr.equals(""))
	      subSql.append(" limit " + limitStr);
	    sql.append(subSql);
	    sql.append(" ) p inner join company c on c.cust_id=p.cust_id ");
	    if (!levelId.equals(""))
	      sql.append(" and c.level_id=" + levelId);
	    if (!type.equals(""))
	      sql.append(" and c.type=" + type);
	    if (!custType.equals(""))
	      sql.append(" and c.cust_type=" + custType);
	    sql.append(" inner join user_info u on p.publish_user_id=u.user_id left join area_info ai on substr(p.area_id,1,6)=ai.area_id ");
	    logger.debug("当前product查询的语句为：" + sql.toString());
	    return sql.toString();
	  }

	
	
	/**
	 * 查询产品(供应)的附加属性列表
	 * @param prodId
	 * @return
	 */
	public List<ProductAttrVo> getAttrs(String prodId)
	{
		List<ProductAttrVo>  lpa = jt.query(Query.product_attr_list_SEL_BY_PRDUCT_ID_PRODUCT_ATTR_LIST, new RowMapper<ProductAttrVo>() {
			 public ProductAttrVo mapRow(ResultSet rs, int rowNum)
				throws SQLException {
				 ProductAttrVo pav = new ProductAttrVo();
				 pav.setAttrId(rs.getString("ATTR_ID"));
				 pav.setAttrName(rs.getString("ATTR_NAME"));
				 pav.setAttrValue(rs.getString("ATTR_VALUE"));
				 return pav;
			}
			 }, prodId);
		return lpa;
	}	
	/**
	 * 获取推荐商品(供应)列表
	 * @param begin
	 * @param num
	 * @return
	 */
	public  List<ProductVo> getRecommendProds(int begin,int num)
	{
		String sql = "select  file_path,product_id,product_name ,class_id FROM ( SELECT product_id, product_name, class_id , m.attach_id as attach_id "
    		  +" FROM maxproduct m where  m.display_start_date <= now() AND m.display_end_date >= now() AND m.display = 1 AND m.validity = 1 AND 	m.commend = '1' ORDER BY oper_time DESC  LIMIT " +begin+","+num+
    		  		" ) m LEFT JOIN attach a ON m.attach_id = a.attach_root_id ";
		List<ProductVo> lnc = jt.query(sql.toString(), new RowMapper<ProductVo>() {
			public ProductVo mapRow(ResultSet rs, int rowNum) throws SQLException {
				ProductVo pv = new ProductVo();
				pv.setClassId(rs.getString("class_id"));
				pv.setProductId(rs.getString("product_id"));
				pv.setProductName(rs.getString("product_name"));
				String smallImg = rs.getString("file_path");
				if(smallImg == null || smallImg.trim().length()<1)
					smallImg = defaultLogoImg;
				pv.setSmallImg(smallImg);
				return pv;
			}
		});
		return lnc;
	}
	
	/**
	 * 生成产品(供应)首页
	 * @param prodId
	 * @param context
	 * @return
	 */
	public boolean createIndexHtml(ServletContext context) throws Exception
	{
		boolean flag = false;
		//数据准备
		Map<String,Object> data = new HashMap<String,Object>();
		//生成首页标题
		String webtitle = ps.getParamValue("webtitle");
		data.put("webtitle", "产品供应--"+webtitle);
		data.put("keywords", Constants.SLOGAN);
		data.put("description", Constants.DESCRIPTION);
		//获取更多产品页链接
		ClassVo ncv = pcs.getClassById("100");
		String ncvurl = ClassUtil.getListUrl(ncv,1);
		data.put("prodsmore", ncvurl);
		//获取推荐产品列表
		List<ProductVo> recomProds = queryProducts("100","1","0,6","oper_time");
		data.put("recomProds", recomProds);
		//获取推荐企业列表
		List<CompanyVo> lcoms = cs.queryCompanies("","add_date","0,6","1");
		data.put("lcoms", lcoms);
		
		int begin = 0;
		String limitStr = ""+begin+","+Constants.PAGESIZE;
		List<ProductVo> lpv = queryListProducts("100", "", limitStr, "oper_time");
		data.put("lpv", lpv);
		data.put("prodnum", Constants.PAGESIZE);
		
		//获取产品类别列表
		List<ClassVo> lcv = pcs.queryProductClzs("100", "0,8", true);
		data.put("prodClzs", lcv);
		ClassVo cv = pcs.getClassById("100");
		//获取友情链接列表
		List<LinkVo> llv = ls.queryLinks("100000000000000", "30");
		data.put("frilinks", llv);
		//获取底部信息
		hs.getFooterInfo(data);
		
		hs.crateHTML(context, data, "/"+cv.getIndexModel(), cv.getStorePath()+"/"+cv.getHomeName());
		return flag;
     }
	
	/**
	 * 生成商品的详细内容页
	 * @param prodId
	 * @param context
	 * @return
	 */
	public boolean createContentHtml(String prodId,ServletContext context) throws Exception
	{
		boolean result = false;
		String sql = "select distinct p.*,c.cust_id cust_id,c.CUST_NAME CUST_NAME,c.JURISTIC JURISTIC,c.CUST_TYPE CUST_TYPE,c.COMPANY_ADDRESS ADDRESS,u.contact_name contact_name," +
				"a.file_path file_path,pc.content_model,pc.store_path,pc.article_rule,pc.class_name,c.GROUP_CONTACT_PHONE contact_phone ,c.CELL_PHONE mobile,c.FAX_NBR fax_nbr, " +
				"c.POST_CODE post_code,c.EMAIL email " +
				"from product p left join company c on c.cust_id = p.CUST_ID left join attach a on p.attach_id = a.attach_root_id " +
				"inner join product_class pc on pc.class_id=p.class_id inner join user_info u on u.user_id=p.PUBLISH_USER_ID " +
				"where p.product_id = ? ";
		ProductVo pv = jt.queryForObject(sql,  new RowMapper<ProductVo>() {
			public ProductVo mapRow(ResultSet rs, int rowNum) throws SQLException {
				ProductVo pv = new ProductVo();
				pv.setClassId(rs.getString("class_id"));
				pv.setProductId(rs.getString("product_id"));
				pv.setProductName(rs.getString("product_name"));
				pv.setStorePath(rs.getString("store_path"));
				pv.setContentModel(rs.getString("content_model"));
				pv.setArticleRule(rs.getString("article_rule"));
				pv.setClassName(rs.getString("class_name"));
				pv.setDesc(rs.getString("PRODUCT_ABSTRACT"));
				String smallImg = rs.getString("file_path");
				if(smallImg == null || smallImg.trim().length()<1)
					smallImg = defaultLogoImg;
				pv.setSmallImg(smallImg);
				pv.setPublishUserId(rs.getString("PUBLISH_USER_ID"));
				pv.setStockNum(rs.getInt("num"));
				
				pv.setPrice(rs.getDouble("PRODUCT_PRICE"));
				pv.setLowPrice(rs.getDouble("LOW_TRADE_PRICE"));
				pv.setHighPrice(rs.getDouble("HIGH_TRADE_PRICE"));
				
				String moneyType = rs.getString("MONEYTYPE");
				moneyType = ps.getDataParam("moneytype", moneyType);
				pv.setMoneytype(moneyType);
				String unit = rs.getString("PRODUCT_UNIT");
				unit = ps.getDataParam("product_unit", unit);
				pv.setUnit(unit);
				
				pv.setSaleScope(rs.getString("PRODUCT_SITE"));
				pv.setStartTime(rs.getString("DISPLAY_START_DATE"));
				pv.setEndTime(rs.getString("DISPLAY_END_DATE"));
				pv.setAddr(rs.getString("APPLICABLE_ADDR"));
				pv.setDetail(rs.getString("PRODUCT_DESC"));
				
				CompanyVo com = new CompanyVo();
				com.setCustId(rs.getString("cust_id"));
				com.setCustName(rs.getString("CUST_NAME"));
				com.setCustType(rs.getString("CUST_TYPE"));
				com.setOfficer(rs.getString("JURISTIC"));
				com.setAddress(rs.getString("ADDRESS"));
				com.setContactPhone(rs.getString("contact_phone"));
				com.setMobile(rs.getString("mobile"));
				com.setFaxNbr(rs.getString("fax_nbr"));
				com.setPostCode(rs.getString("post_code"));
				com.setEmail(rs.getString("email"));
				
				UserInfo ui = new UserInfo();
				ui.setContactName(rs.getString("contact_name"));
				
				pv.setUser(ui);
				pv.setCust(com);
				pv.setUrl(ClassUtil.getObjUrl(pv, pv.getProductId()));
				return pv;
			}},prodId);  
		if(pv!=null)
		{
			//数据准备
			Map<String,Object> data = new HashMap<String,Object>();
			//生成首页标题
			String webtitle = ps.getParamValue("webtitle");
			String title = pv.getClassName()+"-"+pv.getProductName()+"-"+webtitle;
			data.put("webtitle",  title);
			data.put("keywords", pv.getClassName());
			data.put("description", pv.getDesc());
			//分类链接
			data.put("pathList", getProdClassUrl(pv.getClassId()));
			//商品详细信息
			data.put("prod", pv);
			List<ProductVo> lpv = queryProducts(pv.getClassId(),"","0,24","oper_time");
			data.put("lpv", lpv);
			//获取底部信息
			hs.getFooterInfo(data);
			
			hs.crateHTML(context, data, "/"+pv.getContentModel(), pv.getUrl());
			result = true;
		}
		return result;
	}
	
	/**
	 * 生成商品(供应)的列表页
	 * @param prodId
	 * @param context
	 * @return
	 */
	public boolean createListHtml(String classId,final int pageNo,ServletContext context) throws Exception
	{
		boolean result = false;
		String sql = "select class_id,class_name,list_rule,home_name,store_path,list_model from product_class where class_id=? ";
		ProductClassVo pcv = jt.queryForObject(sql,  new RowMapper<ProductClassVo>() {
			 public ProductClassVo mapRow(ResultSet rs, int rowNum)
				throws SQLException {
				 ProductClassVo pcv = new ProductClassVo();
				 pcv.setClassId(rs.getString("class_id"));
				 pcv.setListRule(rs.getString("list_rule"));
				 pcv.setStorePath(rs.getString("store_path"));
				 pcv.setHomeName(rs.getString("home_name"));
				 pcv.setListModel(rs.getString("list_model"));
				 pcv.setClassName(rs.getString("class_name"));
				 pcv.setListUrl(ClassUtil.getListUrl(pcv, pageNo));
				 return pcv;
			}},classId);  
		List<ClassVo>  cvls = pcs.queryProductClzs(classId,"0,24",false);
		int begin = (pageNo-1) * Constants.PAGESIZE;
		String limitStr = ""+begin+","+Constants.PAGESIZE;
		List<ProductVo> lpv = queryListProducts(classId, "", limitStr, "oper_time");
		if(pcv!=null && lpv !=null)
		{
			//数据准备
			Map<String,Object> data = new HashMap<String,Object>();
			//生成首页标题
			String webtitle = ps.getParamValue("webtitle");
			String title = pcv.getClassName()+"-"+webtitle;
			data.put("webtitle",  title);
			data.put("keywords", Constants.KEYWORDS);
			data.put("description", Constants.DESCRIPTION);
			//分类导航链接
			data.put("pathList", getProdClassUrl(pcv.getClassId()));
			//分类链接
			data.put("classList", cvls);
			data.put("lpv", lpv);
			
			List<CompanyVo> coms = cs.queryCompanies("100","add_date","0,3","1");
			data.put("coms", coms);
			//获得当前类别的总商品数
			int num = countProdNumByClass(pcv.getClassId());
			data.put("totalprod", num);
			int totPage = num%Constants.PAGESIZE==0 ? num/Constants.PAGESIZE : num/Constants.PAGESIZE+1;
			PageTool pt = new PageTool(pageNo,totPage,ClassUtil.getListUrl(pcv));
			data.put("pageBar", pt.getPageBar());
			data.put("pagego", pt.getPreNext());
			//获取底部信息
			hs.getFooterInfo(data);
			
			hs.crateHTML(context, data, "/"+pcv.getListModel(), pcv.getListUrl());
			result = true;
		}
		return result;
	}
	/**
	 * 根据当前商品类别获取分类导航列表
	 * @param classId
	 * @return
	 */
	private List<ProductClassVo> getProdClassUrl(String classId)
	{
		String sql = "select class_name,class_id,list_rule,store_path from product_class where substr(?,1,3)=class_id or substr(?,1,6)=class_id or substr(?,1,10)=class_id or substr(?,1,15)=class_id order by class_id asc";
		List<ProductClassVo> lpcs = jt.query(sql,  new RowMapper<ProductClassVo>() {
			 public ProductClassVo mapRow(ResultSet rs, int rowNum)
				throws SQLException {
				 ProductClassVo pcv = new ProductClassVo();
				 pcv.setClassId(rs.getString("class_id"));
				 pcv.setClassName(rs.getString("class_name"));
				 pcv.setListRule(rs.getString("list_rule"));
				 pcv.setStorePath(rs.getString("store_path"));
				 pcv.setListUrl(ClassUtil.getListUrl(pcv, 1));
				 return pcv;
			}
		},classId,classId,classId,classId);   
		return lpcs;
	}
	/**
	 * 统计类别包含的产品(供应)总数
	 * @param classId
	 * @return
	 */
	private int countProdNumByClass(String classId)
	{
		String sql = " select count(*) from product where  display_start_date <=now() and display_end_date >=now() and display=1 and validity =1 and class_id like ? ";
	    int num = jt.queryForInt(sql,classId+"%");
	    return num;
	}
	
	/**
	 * 根据条件查询列表页面的产品(供应)列表
	 * 
	 * @param classId
	 * @param limitStr
	 * @param orderCln
	 * @param commend
	 * @return
	 */
	public List<ProductVo> queryListProducts(String classId,String commend ,String limitStr,
			String orderCln) {
	   StringBuffer sql = new StringBuffer("select distinct p.product_id,p.product_name,p.PRODUCT_UNIT PRODUCT_UNIT,p.PRODUCT_PRICE price,p.attach_id,a.file_path prod_photo,p.class_id,p.MONEYTYPE,p.oper_time,p.small_img,p.big_img,pc.list_rule,pc.store_path,pc.article_rule,p.commend,p.good_product,p.list_top,p.num , ");
	   sql.append(" u.qq qq , p.PRODUCT_ABSTRACT ABSTRACT,p.PRODUCT_SITE,p.APPLICABLE_ADDR,c.cust_id cust_id,c.CUST_NAME CUST_NAME,c.JURISTIC JURISTIC,c.CUST_TYPE CUST_TYPE,c.COMPANY_ADDRESS ADDRESS,c.LEVEL LEVEL from product p join company c on c.cust_id = p.CUST_ID inner join user_info u on u.user_id=p.PUBLISH_USER_ID " +
	   		" left join attach a on p.attach_id = a.attach_root_id inner join product_class pc on pc.class_id=p.class_id where p.display_start_date <=now() and p.display_end_date >=now() and p.display=1 and p.validity =1 ");
	   
	    if (!classId.equals(""))
	    	sql.append(" and p.class_id like '" + classId + "%'");
	    if (!commend.equals(""))
	    	sql.append(" and p.commend=" + commend);
	    if (orderCln.equals(""))
	        sql.append("  order by p." +orderCln +" desc");
	    if (!limitStr.equals(""))
	    	sql.append(" limit " + limitStr );
		List<ProductVo> lnc = jt.query(sql.toString(), new RowMapper<ProductVo>() {
			public ProductVo mapRow(ResultSet rs, int rowNum) throws SQLException {
				ProductVo pv = new ProductVo();
				pv.setClassId(rs.getString("class_id"));
				pv.setProductId(rs.getString("product_id"));
				pv.setListRule(rs.getString("list_rule"));
				pv.setStorePath(rs.getString("store_path"));
				pv.setArticleRule(rs.getString("article_rule"));
				pv.setProductName(rs.getString("product_name"));
				pv.setPrice(rs.getDouble("price"));
				pv.setOperTime(rs.getString("oper_time"));
				String desc = rs.getString("ABSTRACT") != null ?rs.getString("ABSTRACT") : " ";
				pv.setDesc(desc);
				String moneyType = rs.getString("MONEYTYPE");
				moneyType = ps.getDataParam("moneytype", moneyType);
				pv.setMoneytype(moneyType);
				String unit = rs.getString("PRODUCT_UNIT");
				unit = ps.getDataParam("product_unit", unit);
				pv.setUnit(unit);
				
				String smallImg = rs.getString("prod_photo");
				if(smallImg == null || smallImg.trim().length()<1)
					smallImg = defaultLogoImg;
				pv.setSmallImg(smallImg);
				
				pv.setCommend(rs.getString("commend"));
				pv.setBigImg(rs.getString("big_img"));
				pv.setNum(rs.getString("num"));
                pv.setListTop(rs.getString("list_top"));
				pv.setUrl(ClassUtil.getObjUrl(pv, pv.getProductId()));
				pv.setSaleScope(rs.getString("PRODUCT_SITE"));
				pv.setAddr(rs.getString("APPLICABLE_ADDR"));
				
				CompanyVo com = new CompanyVo();
				com.setCustId(rs.getString("cust_id"));
				com.setCustName(rs.getString("CUST_NAME"));
				com.setCustType(rs.getString("CUST_TYPE"));
				com.setOfficer(rs.getString("JURISTIC"));
				com.setAddress(rs.getString("ADDRESS"));
				
				String level = rs.getString("LEVEL");
				level = ps.getCustLevelLogo(level);
				com.setLevelLogo(level);
				
				pv.setCust(com);
				
				UserInfo ui = new UserInfo();
				String qq = rs.getString("qq");
				if(qq==null || qq.trim().length()<=0)
					qq = "";
				ui.setQq(qq);
				
				pv.setUser(ui);
				return pv;
			}
		});
		return lnc;
	}
}
